﻿Imports System.Data.OleDb

''' <summary>
''' Simple DataGateway inspired by corresponding Martin Fowler's pattern:
''' http://martinfowler.com/eaaCatalog/tableDataGateway.html
''' </summary>
Public MustInherit Class DataGateway

    Protected MustOverride ReadOnly Property FindAllQuery() As String

    Protected MustOverride ReadOnly Property DeleteQuery() As String

    Protected MustOverride ReadOnly Property SiteUserQuery() As String

    Protected ReadOnly Property ConnectionString() As String
        Get
            Return ConfigurationManager.ConnectionStrings("fishTankConnection").ConnectionString
        End Get
    End Property

    Public Overridable Function HasOwnership(ByVal ID As ULong, ByVal username As String) As Boolean
        Dim siteUser As DataRow = GetSiteUser(ID)
        Return siteUser("UserName") = username
    End Function

    Public Overridable Function GetSiteUser(ByVal ID As ULong) As DataRow
        Dim queryParams As New OrderedDictionary()
        queryParams.Add("@ID", ID)
        Return QueryTable(SiteUserQuery, queryParams).Rows(0)
    End Function

    Public Function Exists(ByVal ID As ULong) As Boolean
        Return FindByID(ID).Rows.Count() = 1
    End Function

    Public Overridable Function FindByID(ByVal ID As ULong) As DataTable
        Dim sqlQuery As String = FindAllQuery & " WHERE ID=@ID"
        Dim queryParams As New OrderedDictionary()
        queryParams.Add("@ID", ID)
        Return QueryTable(sqlQuery, queryParams)
    End Function

    Public Overridable Function FindAll(Optional ByVal orderBy As OrderCriterion = Nothing) As DataTable
        Dim sqlQuery As String = FindAllQuery
        If Not orderBy Is Nothing Then
            sqlQuery &= " ORDER BY " & orderBy.ToString()
        End If
        Return QueryTable(sqlQuery)
    End Function

    Public Overridable Function FindWhere(ByVal filterBy As FilterCriterion, Optional ByVal orderBy As OrderCriterion = Nothing) As DataTable
        Dim where As String = String.Format(" WHERE {0} LIKE '%' + @ColumnValue + '%'", filterBy.Column)
        Dim sqlQuery As String = FindAllQuery & where
        If Not orderBy Is Nothing Then
            sqlQuery &= " ORDER BY " & orderBy.ToString()
        End If
        Dim queryParams As New OrderedDictionary()
        queryParams.Add("@ColumnValue", filterBy.Value)
        Return QueryTable(sqlQuery, queryParams)
    End Function

    Public Sub DeleteByID(ByVal ID As ULong)
        Dim sqlQuery As String = DeleteQuery & " WHERE ID=@ID"
        Using connection As New OleDbConnection(ConnectionString)
            Dim command As New OleDbCommand(sqlQuery, connection)
            command.CommandType = CommandType.Text
            command.Parameters.AddWithValue("@ID", ID)
            connection.Open()
            command.ExecuteNonQuery()
        End Using
    End Sub

    ''' <remarks>
    ''' OleDbCommand doesn’t support named parameters (MS confirmed it: http://support.microsoft.com/kb/316744)
    ''' We have to make sure the order we call the AddWithValue method matches the order of SQL parameters.
    ''' For more info:
    ''' http://sharemypoint.wordpress.com/2008/07/17/oledbcommand-doesnt-support-named-parameters/
    ''' </remarks>
    Protected Function QueryTable(ByVal sqlQuery As String, Optional ByVal queryParams As OrderedDictionary = Nothing) As DataTable
        Dim table As New DataTable()
        Using connection As New OleDbConnection(ConnectionString)
            Dim command As New OleDbCommand(sqlQuery, connection)
            command.CommandType = CommandType.Text
            If Not queryParams Is Nothing Then
                For Each param As DictionaryEntry In queryParams
                    command.Parameters.AddWithValue(param.Key, param.Value)
                Next
            End If
            connection.Open()
            Dim reader As OleDbDataReader = command.ExecuteReader()
            table.Load(reader)
        End Using
        Return table
    End Function

    Protected Function GetLastInsertID(ByRef command As OleDbCommand)
        command.CommandText = "SELECT @@IDENTITY"
        Return command.ExecuteScalar()
    End Function
End Class
